<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Working with images</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="PHP, PostgreSQL, database, BYTEA, tutorial, images, learn PostgreSQL PHP ">
<meta name="description" content="In this part of the PostgreSQL PHP tutorial, we work with 
images. We insert an image to the database and retrieve it back.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">

<div>
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

</div>


<div class="content2">

<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Working with images</h1>

<p>
In this chapter of the PostgreSQL PHP tutorial, we will work with image files.
Some people do not agree with putting images into databases. Here we 
only show how to do it. We do not dwell into technical issues of whether to save images in
databases or not. 
</p>

<pre>
testdb=> CREATE TABLE images(id INT PRIMARY KEY, data BYTEA);
</pre>

<p>
For this example, we create a new table called images. For the images, we use
the <code>BYTEA</code> data type. It allows to store binary strings.
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<h2>Inserting images</h2>

<p>
In the first example, we are going to insert an image into the 
PostgreSQL database. 
</p>

<pre class="code">
&lt;?php 

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "testdb"; 

$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
    or die ("Could not connect to server\n");

$file_name = "woman.jpg";

$img = fopen($file_name, 'r') or die("cannot read image\n");
$data = fread($img, filesize($file_name));

$es_data = pg_escape_bytea($data);
fclose($img);

$query = "INSERT INTO images(id, data) Values(1, '$es_data')";
pg_query($con, $query); 

pg_close($con); 

?&gt;
</pre>

<p>
We read an image from the current working directory 
and write it into the images table of the PostgreSQL 
testdb database. 
</p>

<pre class="explanation">
$file_name = "woman.jpg";
</pre>

<p>
This is the name of the image file that we will insert
into the database. The image is located in the current working
directory.
</p>

<pre class="explanation">
$img = fopen($file_name, 'r') or die("cannot read image\n");
$data = fread($img, filesize($file_name));
</pre>

<p>
We read binary data from the filesystem. 
</p>

<pre class="explanation">
$es_data = pg_escape_bytea($data);
</pre>

<p>
Binary data might have characters that may cause problems when inserting
them into a database table. The <code>pg_escape_bytea()</code> function
escapes the string for insertion into a bytea field. Later, when reading
binary data from the database, the data must be un-escaped. 
</p>

<pre class="explanation">
fclose($img);
</pre>

<p>
The handle pointing to the image file is closed.
</p>

<pre class="explanation">
$query = "INSERT INTO images(id, data) Values(1, '$es_data')";
pg_query($con, $query); 
</pre>

<p>
The image is inserted into the database. 
</p>


<h2>Reading images</h2>

<p>
In this section, we are going to perform the reverse operation.
We will read an image from the database table. 
</p>

<pre class="code">
&lt;?php 

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "testdb"; 

$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
    or die ("Could not connect to server\n");

$query = "SELECT data FROM images WHERE id=1";
$res = pg_query($con, $query) or die (pg_last_error($con)); 

$data = pg_fetch_result($res, 'data');
$unes_image = pg_unescape_bytea($data);

$file_name = "woman2.jpg";
$img = fopen($file_name, 'wb') or die("cannot open image\n");
fwrite($img, $unes_image) or die("cannot write image data\n");
fclose($img);

pg_close($con); 

?&gt;
</pre>

<p>
We read image data from the images table and write it
to another file, which we call woman2.jpg. 
</p>

<pre class="explanation">
$query = "SELECT data FROM images WHERE id=1";
</pre>

<p>
This line is a SQL SELECT statement to retrieve the image data 
from the table.
</p>

<pre class="explanation">
$data = pg_fetch_result($res, 'data');
</pre>

<p>
We fetch the data from the data column of the images table.
</p>

<pre class="explanation">
$unes_image = pg_unescape_bytea($data);
</pre>

<p>
When we inserted the image data into the database, we have escaped
it. Now we have to un-escape it back to the original. 
</p>

<pre class="explanation">
$file_name = "woman2.jpg";
$img = fopen($file_name, 'wb') or die("cannot open image\n");
</pre>

<p>
We open a file for writing. The new file name will be woman2.jpg.
</p>

<pre class="explanation">
fwrite($img, $unes_image) or die("cannot write image data\n");
</pre>

<p>
The data is written to the filesystem.
</p>

<p>
This part of the PostgreSQL PHP tutorial was dedicated to reading and writing 
images. 
</p>

<div class="g-plusone"></div>

<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>

<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified May 3, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

